package com.catal.framework.sequence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;


/**
 * 序列号发生器的数据访问对象
 * 
 */
public class SequenceDao extends JdbcDaoSupport {
	
	private static final Log log = LogFactory.getLog(SequenceDao.class);
	
	/**
	 * 根据序列号id获取序列号
	 * @param noid 序列号id
	 * @return null 如果不存在记录
	 */
	public No getNo(String noid) {
		String sql = "SELECT NOID,NONAME,NOLENGTH,NOTYPE,PREFIX,POSTFIX,BUFFERSIZE FROM SYS_NO WHERE NOID=?";
		Map m = null;
		No no = null;
		try {
			m = getJdbcTemplate().queryForMap(sql, new Object[] { noid });
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}
		no = new No();
		no.setNoid((String) m.get("noid"));
		no.setNoname((String) m.get("noname"));
		no.setNolength(Integer.parseInt(m.get("nolength").toString()));
		no.setNotype(Integer.parseInt(m.get("notype").toString()));
		no.setPrefix((String) m.get("prefix"));
		no.setPostfix((String) m.get("postfix"));
		// Long long1 = (Long) m.get("buffersize");
		Object object = m.get("buffersize");
		int buffersize = object == null ? 0 : Integer.parseInt(object.toString());
		no.setBufferSize(buffersize);

		return no;
	}

	/**
	 * 获取所有序列号No
	 * @return key 序列号id value No
	 */
	public Map/*<String,No> */getAllNo() {
		String sql = "SELECT NOID,NONAME,NOLENGTH,NOTYPE,PREFIX,POSTFIX,BUFFERSIZE FROM SYS_NO";
		Map m = new HashMap();
		Connection con = null;
		// 把SYS_NO表的数据放入MAP
		PreparedStatement noStat;
		try {
			con = getConnection();
			noStat = con.prepareStatement(sql);

			ResultSet noRs = noStat.executeQuery();
			while (noRs.next()) {
				No no = new No();
				no.setNoid(noRs.getString("noid"));
				no.setNoname(noRs.getString("noname"));
				no.setNolength(noRs.getInt("nolength"));
				no.setNotype(noRs.getInt("notype"));
				no.setPrefix(noRs.getString("prefix"));
				no.setPostfix(noRs.getString("postfix"));
				no.setBufferSize(noRs.getInt("buffersize"));
				m.put(no.getNoid(), no);
			}
			noRs.close();
			noStat.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			releaseConnection(con);
		}

		return m;
	}

	/**
	 * 获取所有序列号明细Instance
	 * @param defaultBufferSize
	 * @return
	 */
	public Map/* <String,Instance> */getAllInstance(int defaultBufferSize) {
		String ins_sql = "SELECT NOID,NOYEAR,NOMONTH,NODAY,NEXTID,PREFIX,POSTFIX FROM SYS_NODTL";
		Connection con = getConnection();
		Map m = new HashMap();

		PreparedStatement insStat;
		try {
			insStat = con
					.prepareStatement(ins_sql, ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);

			ResultSet insRs = insStat.executeQuery();

			while (insRs.next()) {
				Instance ins = new Instance();
				ins.setNoid(insRs.getString("noid"));
				ins.setYear(insRs.getInt("noyear"));
				ins.setMonth(insRs.getInt("nomonth"));
				ins.setDay(insRs.getInt("noday"));

				ins.setLeftNum(0);
				long nextId = insRs.getLong("nextid");
				ins.setNextId(nextId);

				m.put(ins.getNoid(), ins);

			}
			insRs.close();
			insStat.close();

		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			releaseConnection(con);
		}
		return m;
	}

	/**
	 * 更新序列号明细
	 * @param ins
	 */
	public void updateInstance(Instance ins) {
		String update_instance_sql = "UPDATE SYS_NODTL SET NOYEAR=?,NOMONTH=?,NODAY=?,NEXTID=? WHERE NOID = ? AND NOYEAR=? AND NOMONTH=? AND NODAY=?";

		getJdbcTemplate()
				.update(
						update_instance_sql,
						new Object[] { new Integer(ins.getYear()),
								new Integer(ins.getMonth()),
								new Integer(ins.getDay()),
								new Long(ins.getNextId()), ins.getNoid(),
								new Integer(ins.getYear()),
								new Integer(ins.getMonth()),
								new Integer(ins.getDay()) });

	}

	/**
	 * 如果没有数据，返回null
	 * 
	 * @param noid
	 * @param year
	 * @param month
	 * @param day
	 * @return
	 */
	public Instance getInstance(String noid, int year, int month, int day) {
		Instance ins = null;

		try {
			Map m = getJdbcTemplate()
					.queryForMap(
							"SELECT NOID,NOYEAR,NOMONTH,NODAY,NEXTID,PREFIX,POSTFIX FROM SYS_NODTL WHERE NOYEAR=? AND NOMONTH=? AND NODAY=? AND NOID=?",
							new Object[] { new Integer(year),
									new Integer(month), new Integer(day), noid });

			ins = new Instance();

			ins.setDay(day);
			ins.setMonth(month);
			ins.setNextId(Long.valueOf(m.get("nextid").toString()).longValue());
			ins.setNoid(noid);
			ins.setYear(year);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}

		return ins;
	}

	/**
	 * 更新序列号
	 * @param no
	 */
	public void updateNo(No no) {
		String sql = "UPDATE SYS_NO SET NONAME=?,NOLENGTH=?,NOTYPE=? WHERE NOID = ?";
		getJdbcTemplate().update(
				sql,
				new Object[] { no.getNoname(), new Integer(no.getNolength()),
						new Integer(no.getNotype()), no.getNoid() });
	}

	/**
	 * @param noid
	 */
	public void lockNo(String noid) {
		String sql = "UPDATE SYS_NO SET NONAME=NONAME WHERE NOID=?";
		getJdbcTemplate().update(sql, new Object[] { noid });
	}

	/**
	 * 新增序列号明细
	 * @param ins
	 */
	public void insertInstance(Instance ins) {
		String sql = "INSERT INTO SYS_NODTL(NOYEAR,NOMONTH,NODAY,NEXTID,NOID) VALUES(?,?,?,?,?)";
		getJdbcTemplate().update(
				sql,
				new Object[] { new Integer(ins.getYear()),
						new Integer(ins.getMonth()), new Integer(ins.getDay()),
						new Long(ins.getNextId()), ins.getNoid() });
		if(log.isDebugEnabled()){
			log.debug("insertInstance:"+ins);
		}
	}

	/**
	 * 新增序列号
	 */
	public void insertNo(No no) throws DataAccessException {
		String sql = "INSERT INTO SYS_NO(NONAME,NOLENGTH,NOTYPE,NOID,PREFIX,POSTFIX,buffersize) VALUES(?,?,?,?,?,?,?)";
		getJdbcTemplate().update(
				sql,
				new Object[] { no.getNoname(), new Integer(no.getNolength()),
						new Integer(no.getNotype()), no.getNoid(),
						no.getPrefix(), no.getPostfix(),
						new Integer(no.getBufferSize()) });
		if(log.isDebugEnabled()){
			log.debug("insertNo:"+no);
		}
	}
}
